﻿using SqlPerfomance.V2.DomainModel.Enums;
using SqlPerfomance.V2.DomainModel.Models;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SqlPerfomance.V2.Helpers
{
	public class DatabaseInitHelper
	{
		#region StoredProcedures
		private const string MySqlDropProc = @"DROP PROCEDURE IF EXISTS NsTreeUpdateProc;";

		private const string MySqlUpdateProc = @"
CREATE PROCEDURE NsTreeUpdateProc{0} ()
BEGIN
DECLARE current_Category_ID INT;
DECLARE current_parent INT;
DECLARE myLeft INT;

UPDATE  {0} 
SET     lft = 1
        , rgt = 2
WHERE   parent_id IS NULL;

UPDATE  {0} 
SET     lft = NULL
        , rgt = NULL
WHERE   parent_id IS NOT NULL;

WHILE EXISTS (SELECT * FROM {0} WHERE lft IS NULL) DO
	BEGIN
	DECLARE Count INT;
	  SET Count := (SELECT COUNT(*) FROM {0} WHERE lft IS NOT NULL);
	  SET current_Category_ID := (SELECT MAX(nc.id)
	  FROM    {0} nc
			  INNER JOIN {0} nc2 ON nc2.id = nc.parent_id
	  WHERE   nc.lft IS NULL
			  AND nc2.lft IS NOT NULL);

	  SET current_parent := (SELECT  parent_id
	  FROM    {0}
	  WHERE   id = current_category_id);

	  SET myLeft := (SELECT  lft
	  FROM    {0}
	  WHERE   id = current_parent);

	  UPDATE {0} SET rgt = rgt + 2 WHERE rgt > myLeft;
	  UPDATE {0} SET lft = lft + 2 WHERE lft > myLeft;
	  UPDATE {0} SET lft = myLeft + 1, rgt = myLeft + 2 WHERE id = current_category_id;

	END;
END WHILE;
END";

		private const string SqlServerDropProc = @"IF OBJECT_ID('NsTreeUpdateProc') IS NOT NULL
	DROP PROCEDURE NsTreeUpdateProc";

		private const string SqlServerUpdateProc = @"
CREATE PROCEDURE NsTreeUpdateProc
AS
BEGIN
DECLARE @current_Category_ID INTEGER
DECLARE @current_parent INTEGER
DECLARE @myLeft INTEGER

UPDATE  {0} 
SET     lft = 1
        , rgt = 2
WHERE   parent_id IS NULL

UPDATE  {0} 
SET     lft = NULL
        , rgt = NULL
WHERE   parent_id IS NOT NULL

WHILE EXISTS (SELECT * FROM {0} WHERE lft IS NULL) 
BEGIN
DECLARE @Count INT
 SELECT @Count = COUNT(*) FROM {0} WHERE lft IS NOT NULL
	print @Count
  SELECT  @current_Category_ID = MAX(nc.id)
  FROM    {0} nc
          INNER JOIN {0} nc2 ON nc2.id = nc.parent_id
  WHERE   nc.lft IS NULL
          AND nc2.lft IS NOT NULL

  SELECT  @current_parent = parent_id
  FROM    {0}
  WHERE   id = @current_category_id

  SELECT  @myLeft = lft
  FROM    {0}
  WHERE   id = @current_parent

  UPDATE {0} SET rgt = rgt + 2 WHERE rgt > @myLeft;
  UPDATE {0} SET lft = lft + 2 WHERE lft > @myLeft;
  UPDATE {0} SET lft = @myLeft + 1, rgt = @myLeft + 2 WHERE id = @current_category_id
END
END";

		#endregion


		public static void CreateStoredProcedures()
		{
			using (DbConnection connection = GetDbConnection(ConnectionStrings.SqlServerConnectionString))
			{
				connection.Open();
				using (DbCommand command = connection.CreateCommand())
				{
					command.CommandText = SqlServerDropProc;
					command.ExecuteNonQuery();
				}
				using (DbCommand command = connection.CreateCommand())
				{
					command.CommandText = string.Format(SqlServerUpdateProc, GetNodeTreeTableName(typeof(NestedSetTreeNode)));
					command.ExecuteNonQuery();
				}
			}
			//using (DbConnection connection = GetDbConnection(ConnectionStrings.MySqlConnectionString))
			//{
			//	connection.Open();
			//	using (DbCommand command = connection.CreateCommand())
			//	{
			//		command.CommandText = MySqlDropProc;
			//		command.ExecuteNonQuery();
			//	}
			//	using (DbCommand command = connection.CreateCommand())
			//	{
			//		command.CommandText = string.Format(MySqlUpdateProc, GetNodeTreeTableName(typeof(NestedSetTreeNode)));
			//		command.ExecuteNonQuery();
			//	}
			//}
		}
		
		private static string GetNodeTreeTableName(Type nodeType)
		{
			return ConfigurationManager.AppSettings[nodeType.Name];
		}

		private static DbConnection GetDbConnection(ConnectionStrings connectionStr)
		{
			ConnectionStringSettings configuration =
				ConfigurationManager.ConnectionStrings[connectionStr.ToString()];
			DbProviderFactory factory = DbProviderFactories.GetFactory(configuration.ProviderName);

			DbConnection conn = factory.CreateConnection();
			conn.ConnectionString = configuration.ConnectionString;
			return conn;
		}

	}
}
